# python 3.6
# -*- coding:utf-8 -*-
from src.test.api_request import queryApp

__author__ = 'BH8ANK'

import json
import pymysql

conn = pymysql.connect(
    host='120.27.233.178',  # mysql服务器地址
    port=3306,  # 端口号
    user='root',  # 用户名
    passwd='hxf',  # 密码
    db='yaoqishan',  # 数据库名称
    charset='utf8',  # 连接编码，根据需要填写
)
cur = conn.cursor()  # 创建并返回游标

# 根据文件内容创建表头
# sql_1 = "CREATE TABLE jingweidu (prov  VARCHAR(32),log  VARCHAR(100),lat VARCHAR(100),city VARCHAR(100),clog VARCHAR(100),clat VARCHAR(100));"
# cur.execute(sql_1)#执行上述sql命令，首次运行时，需要执行上面的语句，用于创建table


# a = open(r"data.json", "r", encoding='UTF-8')
# out = a.read()
# tmp = json.dumps(out)
# tmp = json.loads(out)
# x = len(tmp)
# print(tmp)
# print(x)
# i = 0

tmp = queryApp();

x = len(tmp)
#  建表的语句；


# # params_json = json.loads(tmp)
# sql_1 = "CREATE TABLE xx_videos (`id` int(100) NOT NULL AUTO_INCREMENT , "
# items = tmp.items()
# for key, value in items:
#     # print(str(key) + '=' + str(value))
#     if str(key).__eq__('intro'):
#         print("我是 intro " + str(key))
#         sql_1 += str(key) + "  VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,"
#     else:
#         sql_1+= str(key) + "  VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,"
#
# sql_1 +=");"
# print(sql_1)
# cur.execute(sql_1) #执行创建表命令

k = 0

sql_middle = ") values ("
sql_tail = ");"

while k < x:
    #  数据填充的语句
    sql = "insert into xx_videos ( "
    sql_names = ""
    sql_values = ""
    items = tmp[k].items()
    for key, value in items:
        if str(key).__eq__("intro"):
            sql_names += str(key) + " ,"
            sql_values += "'" + str(value).replace(" ","").replace("\r","").replace("\n" ,"") + "',"
            print(sql_names)
            continue

        if str(key).__eq__("langvoicename") | str(key).__eq__("yearname") | str(key).__eq__("areaname") | str(
                key).__eq__("catename"):
            pass
        else:
            if str(key).__eq__("tags"):
                # tags 特殊处理 将里面的tagid 放出来；
                # tags = json.loads(str(value))
                print("我是tags里面的东西：   " + str(value))
                if len(value) > 0:
                    sql_names += " tagid,"
                    sql_values += "\"" + value[0]['tagid'] + "\","
                else:
                    sql_names += " tagid,"
                    sql_values += "'',"
            else:
                if str(key).__eq__("playlist") | str(key).__eq__("downlist"):
                    print("" + str(key))
                    sql_names += str(key) + " ,"
                    sql_values += "\"" + "\","
                else:
                    if str(key).__eq__("actor_tags") | str(key).__eq__("director_tags"):
                        sql_names += str(key) + " ,"
                        sql_values += "\"" + str(value) + "\","
                    else:
                        sql_names += str(key) + " ,"
                        sql_values += "'" + str(value) + "',"

    sql += str(sql_names)[:-1] + sql_middle + str(sql_values)[:-1] + sql_tail
    print(sql)

    k = k + 1
    cur.execute(sql)  # 执行上述sql命令
    conn.commit()

# print(sql_1)
# cur.execute(sql_1) #执行创建表命令

# M = tmp
# name = 'xx_videos'
# j = len(M[name])
# # k = 0
# while k < j:
#     F = [M[name][k]['langvoiceid'],M[name][k]['langvoicename'],M[name][k]['sortnum'],]
#     # print(H[0])
#     sql_2 = "insert into xx_videos (langvoiceid,langvoicename,sortnum) values (" + "'"+F[0]+"'" +","+ "'"+F[1]+"'"  +","+ "'"+F[2]+"'" + ");"
#     print(sql_2)
#     cur.execute(sql_2)  # 执行上述sql命令
#     k = k + 1
#     conn.commit()

print("============")

conn.close()
